package nix.bs.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import nix.bs.domain.Book;
import nix.bs.domain.Category;
import nix.commons.CommonUtils;
import nix.jdbc.TxQueryRunner;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class BookDao {

	private QueryRunner qr = new TxQueryRunner();

	public List<Book> findAll() {
		try {
			return qr.query("select * from tb_book where isdel = false",
					new BeanListHandler<Book>(Book.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public List<Book> findByCategory(String cid) {
		try {
			return qr.query(
					"select * from tb_book where isdel = false and cid = ?",
					new BeanListHandler<Book>(Book.class), cid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public Book findByBid(String bid) {
		try {
			Map<String, Object> map = qr
					.query("select * from tb_book b, tb_category c where b.isdel = false and b.cid = c.cid and b.bid = ?",
							new MapHandler(), bid);
			Book book = CommonUtils.toBean(map, Book.class);
			Category category = CommonUtils.toBean(map, Category.class);
			book.setCategory(category);
			return book;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public int getCountByCid(String cid) {
		try {
			Number number = (Number) qr
					.query("select count(*) from tb_book where isdel = false and cid = ?",
							new ScalarHandler(), cid);
			return number.intValue();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void add(Book book) {
		try {
			Object[] params = { book.getBid(), book.getBname(),
					book.getPrice(), book.getAuthor(), book.getImage(),
					book.getCategory().getCid(), book.isIsdel() };
			qr.update(
					"insert into tb_book (bid, bname, price, author, image, cid, isdel) values (?, ?, ?, ?, ?, ?, ?)",
					params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void delete(String bid) {
		try {
			qr.update("update tb_book set isdel = true where bid = ?", bid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void edit(Book book) {

		try {
			Object[] params = { book.getBname(), book.getPrice(),
					book.getAuthor(), book.getImage(),
					book.getCategory().getCid(), book.isIsdel(), book.getBid() };
			qr.update(
					"update tb_book set bname = ?, price = ?, author = ?, image = ?, cid = ?, isdel = ? where bid = ?",
					params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
